package com.lichao.tools.sql;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlModifier {

    public static void main(String[] args) {
        String sql = "select * from user where a2.user_namessscc  = '${name}' and uages  = ${ages}";

        // Simulate frontend parameters
        Map<String, String> frontendParams = new HashMap<>(2);
        // Empty string means not provided
        frontendParams.put("name", "");
        // Provided value
        frontendParams.put("ages", "25");

        boolean isNameRequired = false;

        String modifiedSql = modifySql(sql, frontendParams, isNameRequired);

        System.out.println("Modified SQL: " + modifiedSql);
    }

    public static String modifySql(String sql, Map<String, String> params, boolean isNameRequired) {
        // Regular expression to match the pattern "column_name='${variable}'" or "column_name=${variable}"
        String pattern = "(?i)(\\b\\w+(\\.\\w+)?\\s*=\\s*(['\"]?)\\$\\{([^}]+)\\}\\3)";
        Pattern conditionPattern = Pattern.compile(pattern);

        Matcher matcher = conditionPattern.matcher(sql);

        StringBuilder modifiedSql = new StringBuilder();

        int lastEnd = 0;

        while (matcher.find()) {
            int start = matcher.start();
            int end = matcher.end();

            // Append text before the current condition
            modifiedSql.append(sql.substring(lastEnd, start));

            // Get the variable name and its value
            String paramName = matcher.group(4);
            String paramValue = params.getOrDefault(paramName, "");

            // Check if the parameter is required or has a non-empty value
            if (("name".equals(paramName) && isNameRequired) || !paramValue.isEmpty()) {
                // Keep the condition
                modifiedSql.append(matcher.group());
            } else {
                // Remove the condition and any surrounding whitespace and 'and'/'or'
                String cleanedSql = removeCondition(sql, start, end);
                sql = cleanedSql;
                lastEnd = start;
            }

            lastEnd = end;
        }

        // Append remaining part of the SQL
        modifiedSql.append(sql.substring(lastEnd));

        return cleanUpSql(modifiedSql.toString());
    }

    private static String removeCondition(String sql, int start, int end) {
        // Find the beginning of the condition
        int conditionStart = findConditionStart(sql, start);
        // Find the end of the condition
        int conditionEnd = findConditionEnd(sql, end);

        // Return the SQL without the condition
        return sql.substring(0, conditionStart).trim() + " " + sql.substring(conditionEnd).trim();
    }

    private static int findConditionStart(String sql, int start) {
        for (int i = start - 1; i >= 0; i--) {
            char c = sql.charAt(i);
            if (c == '(' || c == ' ') {
                continue;
            }
            if (c == 'a' && sql.startsWith("and", i)) {
                return i + 3;
            }
            if (c == 'o' && sql.startsWith("or", i)) {
                return i + 2;
            }
            break;
        }
        return 0;
    }

    private static int findConditionEnd(String sql, int end) {
        for (int i = end; i < sql.length(); i++) {
            char c = sql.charAt(i);
            if (c == ')' || c == ' ') {
                continue;
            }
            if (c == 'a' && sql.startsWith("and", i)) {
                return i;
            }
            if (c == 'o' && sql.startsWith("or", i)) {
                return i;
            }
            break;
        }
        return sql.length();
    }

    private static String cleanUpSql(String sql) {
        // Remove extra spaces and ensure correct spacing around keywords
        sql = sql.replaceAll("\\s+", " ");
        sql = sql.replaceAll("\\s*(where|and|or)\\s*", " $1 ");
        sql = sql.trim();
        // Remove trailing 'where' if no conditions are left
        if (sql.toLowerCase().endsWith(" where")) {
            sql = sql.substring(0, sql.length() - " where".length()).trim();
        }
        return sql;
    }
}